SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Marco Torres
-- Create date: 11/06/2012
-- Description:	Obtener un listado de proyectos.
-- Proyect_R 'a','','',0,0,0,10,'Name','ASC'
-- =============================================
CREATE PROCEDURE Proyect_R	
@Name NVARCHAR(200),
@DateStart NVARCHAR(20),
@DateEnd NVARCHAR(20),
@Hours INT,
@StatusId INT, 
@Start INT, 
@Limit INT, 
@Order NVARCHAR(50),
@Direction NVARCHAR(5)
	
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT  ROW_NUMBER() OVER ( ORDER BY 
					 CASE WHEN @Order = 'Name'AND @Direction = 'ASC' THEN Nombre END ASC,	      
					 CASE WHEN @Order = 'DateStart'AND @Direction = 'ASC' THEN fechaInicio END ASC,	      
					 CASE WHEN @Order = 'DateEnd'AND @Direction = 'ASC' THEN fechaFin END ASC,	      
					 CASE WHEN @Order = 'Hours'AND @Direction = 'ASC' THEN horas END ASC,	      
					 CASE WHEN @Order = 'StatusDescription'AND @Direction = 'ASC' THEN descripcion END ASC,	      					 					 

					 CASE WHEN @Order = 'Name'AND @Direction = 'DESC' THEN Nombre END DESC,	      
					 CASE WHEN @Order = 'DateStart'AND @Direction = 'DESC' THEN fechaInicio END DESC,	      
					 CASE WHEN @Order = 'DateEnd'AND @Direction = 'DESC' THEN fechaFin END DESC,	      
					 CASE WHEN @Order = 'Hours'AND @Direction = 'DESC' THEN horas END DESC,	      
					 CASE WHEN @Order = 'StatusDescription'AND @Direction = 'DESC' THEN descripcion END DESC) AS RowNum, 
				P.Id, Nombre, fechaInicio, fechaFin, horas, E.descripcion
		  INTO #Proyecto
		  FROM Proyecto P 
			INNER JOIN Estatus E on P.estatusId = e.id
			WHERE (Nombre LIKE '%'+@Name+'%' OR @Name = '')
			AND (fechaInicio  = @DateStart OR @DateStart = '')
			AND (fechaFin = @DateEnd OR @DateEnd = '' ) 
			AND (@Hours IN (horas, 0))
			AND (@StatusId IN (estatusId, 0))			
			AND P.Activo = 1					
			
		Select COUNT(id)AS TotalReg From #Proyecto

		SELECT Id, nombre, fechaInicio, fechaFin, horas, descripcion  		
		FROM #Proyecto
		WHERE RowNum BETWEEN @Start + 1 AND (@Start + @Limit)
SET NOCOUNT OFF;
END
GO

